package ar.com.larctest.dbHelper;

import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class SQLLARCTESTHelper extends SQLiteOpenHelper {
	private static final String DATABASE_NAME = "larctest.db";
	private static final int DB_VERSION_NUMBER = 1;

	//Master Data
	public static final String DB_HORSES_TABLE_NAME = "horses";
	public static final String DB_HORSES_RACING_TABLE_NAME = "horses_racing";
    public static final String DB_TRACK_TABLE_NAME = "track";
    public static final String DB_HUMIDITY_TABLE_NAME = "humidity";
    public static final String DB_TEMPERATURE_TABLE_NAME = "temperature";
    public static final String DB_WORK_TABLE_NAME = "work";

    //Measurement Data
    public static final String DB_MEASUREMENT_TABLE_NAME = "measurement";
    public static final String DB_MEASUREMENT_ITEM_TABLE_NAME = "measurement_item";

    //Temp Tables
    public static final String DB_MEASUREMENT_TEMP_TABLE_NAME = "measurement_temp";
    public static final String DB_MEASUREMENT_TEMP_ITEM_TABLE_NAME = "measurement_item_temp";

    private static final String DB_WORK_CREATE_SCRIPT = "CREATE TABLE " +
                                                                        DB_WORK_TABLE_NAME +
                                                                        " (idWork INTEGER PRIMARY KEY, " +
                                                                        "  description VARCHAR(30)" +
                                                                        ")";

    private static final String DB_WORK_INSERT_SCRIPT = "INSERT INTO " + DB_WORK_TABLE_NAME + " SELECT 1 AS 'idWork', 'LARGO' AS 'description' " +
                                                        "UNION SELECT 2, 'LIVIANO' " +
                                                        "UNION SELECT 3, 'MEDIO CORRER' " +
                                                        "UNION SELECT 4, 'PARTIDA' " +
                                                        "UNION SELECT 5, 'FLOREO' ";


    private static final String DB_TEMPERATURE_CREATE_SCRIPT = "CREATE TABLE " +
                                                                        DB_TEMPERATURE_TABLE_NAME +
                                                                        " (idTemperature INTEGER PRIMARY KEY, " +
                                                                        "  description VARCHAR(30)" +
                                                                        ")";

    private static final String DB_TEMPERATURE_INSERT_SCRIPT = "INSERT INTO " + DB_TEMPERATURE_TABLE_NAME + " SELECT 1 AS 'idTemperature', '4-10' AS 'description' " +
                                                               "UNION SELECT 2, '10-20' " +
                                                               "UNION SELECT 3, '20-30' " +
                                                               "UNION SELECT 4, '30-40' ";

    private static final String DB_HUMIDITY_CREATE_SCRIPT = "CREATE TABLE " +
                                                                        DB_HUMIDITY_TABLE_NAME +
                                                                        " (idHumidity INTEGER PRIMARY KEY, " +
                                                                        "  description VARCHAR(30)" +
                                                                        ")";

    private static final String DB_HUMIDITY_INSERT_SCRIPT = "INSERT INTO " + DB_HUMIDITY_TABLE_NAME + " SELECT 1 AS idHumidity, '0-33' AS 'description' " +
                                                            "UNION SELECT 2, '33-66' " +
                                                            "UNION SELECT 3, '66-100' ";

    private static final String DB_TRACK_CREATE_SCRIPT = "CREATE TABLE " +
                                                                        DB_TRACK_TABLE_NAME +
                                                                        " (idTrack INTEGER PRIMARY KEY, " +
                                                                        "  description VARCHAR(30)" +
                                                                        ")";

    private static final String DB_TRACK_INSERT_SCRIPT = "INSERT INTO " + DB_TRACK_TABLE_NAME + " SELECT 1 AS 'idTrack', 'ARENA' AS 'description' " +
                                                         "UNION SELECT 2, 'CESPED' " +
                                                         "UNION SELECT 3, 'CIRCUITO' ";

    private static final String DB_HORSES_CREATE_SCRIPT = "CREATE TABLE " +
                                                                        DB_HORSES_TABLE_NAME +
                                                                        " (idHorse INTEGER PRIMARY KEY AUTOINCREMENT, " +
                                                                        "  name VARCHAR(60), " +
                                                                        "  yearsOld INTEGER, " +
                                                                        "  hair VARCHAR(80), " +
                                                                        "  temperament VARCHAR(60), " +
                                                                        "  state VARCHAR(1), " +
                                                                        "  idUser INTEGER, " +
                                                                        "  idHorseWeb INTEGER " +
                                                                        ")";

    private static final String DB_HORSES_RACING_CREATE_SCRIPT = "CREATE TABLE " +
                                                                        DB_HORSES_RACING_TABLE_NAME +
                                                                        " (idHorseRacing INTEGER PRIMARY KEY, " +
                                                                        "  idHorse INTEGER, " +
                                                                        "  dateRacing DATE, " +
                                                                        "  distance DOUBLE " +
                                                                        ")";

    private static final String DB_MEASUREMENT_CREATE_SCRIPT = "CREATE TABLE " +
                                                                        DB_MEASUREMENT_TABLE_NAME +
                                                                        " (idMeasurement INTEGER PRIMARY KEY AUTOINCREMENT, " +
                                                                        "  dateMeasurement DATE, " +
                                                                        "  idTrack INTEGER, " +
                                                                        "  idTemperature INTEGER, " +
                                                                        "  idHumidity INTEGER, " +
                                                                        "  idUser INTEGER " +
                                                                        ")";

    private static final String DB_MEASUREMENT_ITEM_CREATE_SCRIPT = "CREATE TABLE " +
                                                                        DB_MEASUREMENT_ITEM_TABLE_NAME +
                                                                        " (idMeasurementItem INTEGER PRIMARY KEY AUTOINCREMENT, " +
                                                                        "  idMeasurement INTEGER, " +
                                                                        "  idHorse INTEGER, " +
                                                                        "  initTime DATETIME, " +
                                                                        "  finishTime DATETIME, " +
                                                                        "  lacticAcid1 DOUBLE, " +
                                                                        "  heartRate1 DOUBLE, " +
                                                                        "  lacticAcid2 DOUBLE, " +
                                                                        "  heartRate2 DOUBLE, " +
                                                                        "  lacticAcid3 DOUBLE, " +
                                                                        "  heartRate3 DOUBLE, " +
                                                                        "  respiratoryRate3 DOUBLE, " +
                                                                        "  idWork INTEGER, " +
                                                                        "  meters DOUBLE, " +
                                                                        "  time DOUBLE " +
                                                                        ")";

    private static final String DB_MEASUREMENT_TEMP_CREATE_SCRIPT = "CREATE TABLE " +
                                                                        DB_MEASUREMENT_TEMP_TABLE_NAME +
                                                                        " (idTrack INTEGER, " +
                                                                        "  idTemperature INTEGER, " +
                                                                        "  idHumidity INTEGER " +
                                                                        ")";

    private static final String DB_MEASUREMENT_TEMP_ITEM_CREATE_SCRIPT = "CREATE TABLE " +
                                                                        DB_MEASUREMENT_TEMP_ITEM_TABLE_NAME +
                                                                        " (idHorse INTEGER, " +
                                                                        "  nameHorse VARCHAR(60), " +
                                                                        "  initTime DATETIME, " +
                                                                        "  finishTime DATETIME, " +
                                                                        "  timeOut1 VARCHAR(5), " +
                                                                        "  timeOut2 VARCHAR(5), " +
                                                                        "  timeOut3 VARCHAR(5), " +
                                                                        "  lacticAcid1 DOUBLE, " +
                                                                        "  heartRate1 DOUBLE, " +
                                                                        "  lacticAcid2 DOUBLE, " +
                                                                        "  heartRate2 DOUBLE, " +
                                                                        "  lacticAcid3 DOUBLE, " +
                                                                        "  heartRate3 DOUBLE, " +
                                                                        "  respiratoryRate3 DOUBLE, " +
                                                                        "  idWork INTEGER, " +
                                                                        "  meters DOUBLE, " +
                                                                        "  time DOUBLE, " +
                                                                        "  state VARCHAR(1) " +
                                                                        ")";

	private static SQLLARCTESTHelper instance;

    public static synchronized SQLLARCTESTHelper getHelper(Context context)
    {
        if (instance == null) {
            instance = new SQLLARCTESTHelper(context);
            instance.getWritableDatabase().execSQL("PRAGMA read_uncommitted = true;");
            instance.getWritableDatabase().execSQL("PRAGMA synchronous = OFF");
        }

        return instance;
    }

	private SQLLARCTESTHelper(Context context) {
		super(context, DATABASE_NAME, null, DB_VERSION_NUMBER);
	}
	
	public void dbClose() {
		this.getWritableDatabase().close();
	}
	public void dbOpen() throws SQLException {
		this.getWritableDatabase();
	}
	
	@Override
	public void onCreate(SQLiteDatabase db) {
		try {
			Log.i("onCreate", "Creating the database...");
			db.execSQL(DB_HORSES_CREATE_SCRIPT);
			db.execSQL(DB_HORSES_RACING_CREATE_SCRIPT);
            db.execSQL(DB_TRACK_CREATE_SCRIPT);
            db.execSQL(DB_HUMIDITY_CREATE_SCRIPT);
            db.execSQL(DB_WORK_CREATE_SCRIPT);
            db.execSQL(DB_TEMPERATURE_CREATE_SCRIPT);
            db.execSQL(DB_MEASUREMENT_CREATE_SCRIPT);
            db.execSQL(DB_MEASUREMENT_ITEM_CREATE_SCRIPT);
            db.execSQL(DB_MEASUREMENT_TEMP_CREATE_SCRIPT);
            db.execSQL(DB_MEASUREMENT_TEMP_ITEM_CREATE_SCRIPT);

            //INSERT MASTER DATA
            db.execSQL(DB_TRACK_INSERT_SCRIPT);
            db.execSQL(DB_HUMIDITY_INSERT_SCRIPT);
            db.execSQL(DB_TEMPERATURE_INSERT_SCRIPT);
            db.execSQL(DB_WORK_INSERT_SCRIPT);

		} catch (Exception e) {
			Log.e("onCreate", "Error with creating Database Tables: " + e);
		}
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.i("onUpgrade", "Update the database...");
//        db.execSQL(DB_HORSES_CREATE_SCRIPT);
//        db.execSQL(DB_SHORSES_RACING_CREATE_SCRIPT);
	}
	
	public void beginTransaction()
	{
		try {
			this.getWritableDatabase().beginTransactionNonExclusive();
		} catch (Exception e) {
			Log.e("onCreate", "Error in Begintransaction Method: " + e);
		}		
	}
	
	public void commitTransaction()
	{
		try {
			this.getWritableDatabase().setTransactionSuccessful();
			this.getWritableDatabase().endTransaction();
		} catch (Exception e) {
			Log.e("onCreate", "Error in CommitTransaction Method: " + e);
		}		
	}
	
	public void rollbackTransaction()
	{
		try {
			this.getWritableDatabase().endTransaction();
		} catch (Exception e) {
			Log.e("onCreate", "Error in RollbackTransaction Method: " + e);
		}		
	}
}
